from sqlalchemy import create_engine,desc,asc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import Column,  Integer, String, FLOAT, Date
from sqlalchemy.sql import func, or_, and_

Base = declarative_base()
class EMP(Base):
    __tablename__ = 'Emp'

    EMPNO = Column(Integer,primary_key=True, autoincrement=False, nullable=True)
    ENAME = Column(String(10))
    JOB = Column(String(9))
    MGR = Column(Integer)
    HIREDATE = Column(Date)
    SAL = Column(FLOAT(7, 2))
    COMM = Column(FLOAT(7, 2))
    DEPTNO = Column(Integer)

class DEPT(Base):
    __tablename__ = 'Dept'
    DEPTNO = Column(Integer, primary_key=True, autoincrement=False)
    DNAME = Column(String(14), nullable=True)
    LOC = Column(String(13), nullable=True)


class OrmDB(object):
    def __init__(self, username, password, host, port, dbname):
        sql = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(username, password, host, port, dbname)
        engine = create_engine(sql, max_overflow=5)
        Session = sessionmaker(bind=engine) # 创建一个sessionmark对象
        self.session = Session()  # 创建一个session对象，用来调用内置的增删改查命令

    def addObject(self, obj):
        self.session.add_all(obj)
        self.session.commit()


    def findAll(self):
        ret = self.session.query(EMP,DEPT).filter(EMP.DEPTNO==DEPT.DEPTNO).all()
        return ret

    def findById(self, id):
        ret = self.session.query(EMP,DEPT).filter(EMP.DEPTNO==id,EMP.DEPTNO==DEPT.DEPTNO).all()
        return ret
    
    def find_sal(self):
        ret = self.session.query(EMP.DEPTNO,func.avg(EMP.SAL)).group_by(EMP.DEPTNO).order_by(desc(func.avg(EMP.SAL))).all()
        return ret
        #4、使用SQLalchemy，查询所有部门的平均工资，并按照降序排列，采用函数如下：
        # Session.query(funct.avg(字段),obj.条件字段).group_by(条件字段).all()
    def find_left(self):
        ret = self.session.query(EMP.EMPNO,EMP.ENAME,EMP.JOB).join(DEPT,EMP.DEPTNO==DEPT.DEPTNO,isouter=True).all()
        return ret

    def find_five(self):
        ret = self.session.query(EMP).filter(or_(EMP.SAL<3000, EMP.HIREDATE< '1981-10-01')).order_by(desc(EMP.SAL),asc(EMP.HIREDATE)).all()
        return ret
    #使用SQLalchemy，查询岗位薪资低于3000元，或者入职时间晚于1981-10-01的员工姓名、编号，岗位，薪资、入职时间，同时对薪资降序和入职时间升序排列

if __name__ == '__main__':
    db = OrmDB('root', '123456', 'localhost', 3306, 'zuoye')
    ret = db.find_sal()
    print(ret)
    # items = db.findAll()
    # for i in items:
    #     print('姓名：',i[0].ENAME,'编号：',i[0].EMPNO,'薪资：',i[0].SAL,'岗位：',i[0].JOB,'部门名：',i[1].DNAME,'部门地址：',i[1].LOC)
    # itens = db.findById(20)
    # for i in itens:
    #     print('姓名：',i[0].ENAME,'编号：',i[0].EMPNO,'薪资：',i[0].SAL,'岗位：',i[0].JOB,'部门名：',i[1].DNAME,'部门地址：',i[1].LOC,'部门编号:',i[1].DEPTNO)





    # def remove(self, id):
    #     self.session.query(Person).filter_by(pno=id).delete()
    #     self.session.commit()

    # def update_obj(self, person):
    #     self.session.query(Person).filter_by(pno=person.pno).update({'name':person.name,'age':person.age,'address':person.address})
    #     self.session.commit()

    # def search(self, option):
    #     #ret = self.session.query(Person).filter(Person.name.like('%j%')).all()
    #     #ret = self.session.query(Person).order_by(Person.name.desc()).all()
    #     #ret = self.session.query(Person).group_by(Person.pno).all()
    #     ret = self.session.query(Emp,Dept).filter(Emp.deptno==Dept.deptno).all()
    #     return ret






# if __name__ == '__main__':
#     db = OrmDB('root', '123456', 'localhost', 3306, 'zuoye')
#     obj = [
#         EMP(EMPNO=7369,ENAME='SMITH',JOB='CLERK',MGR=7902,HIREDATE='1980-12-17',SAL=800,COMM=None,DEPTNO=20),
#         EMP(EMPNO = 7499, ENAME = 'ALLEN', JOB = 'SALESMAN', MGR = 7698, HIREDATE = '1981-02-20', SAL = 1600, COMM = 300, DEPTNO = 30),
#         EMP(EMPNO = 7521, ENAME = 'WARD', JOB = 'SALESMAN', MGR = 7698, HIREDATE = '1981-02-22', SAL = 1250, COMM = 500, DEPTNO = 30),
#         EMP(EMPNO = 7566, ENAME = 'JONES', JOB = 'MANAGER', MGR = 7839, HIREDATE = '1981-04-02', SAL = 2975, COMM = None, DEPTNO = 20),
#         EMP(EMPNO = 7654, ENAME = 'MARTIN', JOB = 'SALESMAN', MGR = 7698, HIREDATE = '1981-09-28', SAL = 1250, COMM = 1400, DEPTNO = 30),
#         EMP(EMPNO = 7698, ENAME = 'BLAKE', JOB = 'MANAGER', MGR = 7839, HIREDATE = '1981-05-01', SAL = 2850, COMM = None, DEPTNO = 30),
#         EMP(EMPNO = 7782, ENAME = 'CLARK', JOB = 'MANAGER', MGR = 7839, HIREDATE = '1981-06-09', SAL = 2450, COMM = None, DEPTNO = 10),
#         EMP(EMPNO = 7788, ENAME = 'SCOTT', JOB = 'ANALYST', MGR = 7566, HIREDATE = '1987-04-19', SAL = 3000, COMM = None, DEPTNO = 20),
#         EMP(EMPNO = 7839, ENAME = 'KING', JOB = 'PRESIDENT', MGR = None, HIREDATE = '1987-11-17', SAL = 5000, COMM = None, DEPTNO = 10),
#         EMP(EMPNO = 7844, ENAME = 'TURNER', JOB = 'SALESMAN', MGR = 7698, HIREDATE = '1981-09-08', SAL = 1500, COMM = 0, DEPTNO = 30),
#         EMP(EMPNO = 7876, ENAME = 'ADAMS', JOB = 'CLERK', MGR = 7788, HIREDATE = '1987-05-23', SAL = 1100, COMM = None, DEPTNO = 20),
#         EMP(EMPNO = 7900, ENAME = 'JAMES', JOB = 'CLERK', MGR = 7698, HIREDATE = '1987-12-03', SAL = 950, COMM = None, DEPTNO = 30),
#         EMP(EMPNO = 7902, ENAME = 'FORD', JOB = 'ANALYST', MGR = 7566, HIREDATE = '1981-12-03', SAL = 3000, COMM = None, DEPTNO = 20),
#         EMP(EMPNO = 7934, ENAME = 'MILLER', JOB = 'CLERK', MGR = 7782, HIREDATE = '1982-01-23', SAL = 1300, COMM = None, DEPTNO = 10),

#         DEPT(DEPTNO=10,DNAME='ACCOUNTING',LOC='NEW YORK'),
#         DEPT(DEPTNO=20,DNAME='RESEARCH',LOC='DALLAS'),
#         DEPT(DEPTNO=30,DNAME='SALES',LOC='CHICAGO'),
#         DEPT(DEPTNO=40,DNAME='OPERATIONS',LOC='BOSTON'),
#     ]
#     db.addObject(obj)






